/**
 * 
 */
package sk.stuba.fiit.foo07.genex.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import sk.stuba.fiit.foo07.genex.beans.Test;
import sk.stuba.fiit.foo07.genex.exceptions.QuestionPointsExistsException;

/**
 * @author Martin
 * 
 */
public class TestDaoDerby extends BasicDao implements TestDao {

    private ResultSet rs;
    private PreparedStatement pst;
    private Test test;
    private ArrayList<Test> testList;
    private QuestionPointsDao qpDao;

    private static final String sqlAdd = "INSERT INTO TEST VALUES (? , ? , CURRENT_TIMESTAMP , DEFAULT, ? , ? )";
    private static final String sqlAddRoot = "INSERT INTO TEST VALUES (? , ? , CURRENT_TIMESTAMP , DEFAULT, ? , NULL )";
    private static final String sqlDeleteQuestions = "DELETE FROM TESTQUESTION WHERE TESTID = ?";
    private static final String sqlDelete = "DELETE FROM TEST WHERE TESTID = ?";
    private static final String sqlGetById = "SELECT * FROM TEST WHERE TESTID = ?";
    private static final String sqlGetByCategoryId = "SELECT * FROM TEST WHERE CATEGORYID = ?";
    private static final String sqlGetByCategoryIdRoot = "SELECT * FROM TEST WHERE CATEGORYID IS NULL";
    private static final String sqlUpdate = "UPDATE TEST SET NAME = ? , SUBJECT = ? , GENERATED = ? , USERID = ?, CATEGORYID = ? WHERE TESTID = ?";
    private static final String sqlUpdateRoot = "UPDATE TEST SET NAME = ? , SUBJECT = ? , GENERATED = ? , USERID = ?, CATEGORYID = NULL WHERE TESTID = ?";

    public TestDaoDerby() throws Exception {
        super();
    }

    public TestDaoDerby(Connection con) {
        super(con);
        qpDao = new QuestionPointsDaoDerby(con);
    }

    @Override
    public void addTest(Integer testCategoryID, Test toAdd) throws SQLException {
        try {
            if (testCategoryID == null)
                pst = con.prepareStatement(sqlAddRoot,
                        Statement.RETURN_GENERATED_KEYS);
            else
                pst = con.prepareStatement(sqlAdd,
                        Statement.RETURN_GENERATED_KEYS);

            pst.setString(1, toAdd.getName());
            pst.setString(2, toAdd.getSubject());
            pst.setInt(3, toAdd.getUserID());
            if (testCategoryID != null)
                pst.setInt(4, testCategoryID);
            pst.execute();

            rs = pst.getGeneratedKeys();
            rs.next();

            toAdd.setTestID(rs.getInt(1));

            qpDao.addQuestionsPoints(toAdd.getTestID(), toAdd
                    .getQuestionPoints());

        } catch (QuestionPointsExistsException e) {
            qpDao.updateQuestionPoints(toAdd.getTestID(), toAdd
                    .getQuestionPoints());
        } catch (SQLException sqle) {
            pst = null;
            throw sqle;
        }
    }

    @Override
    public void addTests(Integer testCategoryID, ArrayList<Test> toAdd)
            throws SQLException {

        for (Test t : toAdd) {
            this.addTest(testCategoryID, t);
        }

    }

    @Override
    public void deleteTest(Test toDelete) throws SQLException {
        try {
            pst = con.prepareStatement(sqlDeleteQuestions);
            pst.setInt(1, toDelete.getTestID());
            pst.execute();

            pst = con.prepareStatement(sqlDelete);
            pst.setInt(1, toDelete.getTestID());
            pst.execute();

        } catch (SQLException sqle) {
            pst = null;
            throw sqle;
        }
    }

    @Override
    public void deleteTests(ArrayList<Test> toDelete) throws SQLException {

        for (Test t : toDelete) {
            this.deleteTest(t);
        }

    }

    @Override
    public Test getTestByID(Integer testID) throws SQLException {
        try {
            pst = con.prepareStatement(sqlGetById);
            pst.setInt(1, testID);
            rs = pst.executeQuery();
            rs.next();

            test = new Test();
            test.setName(rs.getString("NAME"));
            test.setSubject(rs.getString("SUBJECT"));
            test.setGenerated(rs.getTimestamp("GENERATED"));
            test.setTestID(testID);
            test.setUserID(rs.getInt("USERID"));
            test.setCategoryID(rs.getInt("CATEGORYID"));

            test.setQuestionPoints(qpDao.getQuestionPointsByTestID(test
                    .getTestID()));

            rs.close();

            return test;

        } catch (SQLException sqle) {
            rs = null;
            pst = null;
            test = null;
            throw sqle;
        }
    }

    @Override
    public ArrayList<Test> getTestsByCategoryID(Integer categoryID)
            throws SQLException {
        try {
            if (categoryID == null)
                pst = con.prepareStatement(sqlGetByCategoryIdRoot,
                        ResultSet.TYPE_SCROLL_INSENSITIVE,
                        ResultSet.CONCUR_UPDATABLE);
            else {
                pst = con.prepareStatement(sqlGetByCategoryId,
                        ResultSet.TYPE_SCROLL_INSENSITIVE,
                        ResultSet.CONCUR_UPDATABLE);
                pst.setInt(1, categoryID);
            }

            rs = pst.executeQuery();
            rs.last();
            testList = new ArrayList<Test>(rs.getRow());
            rs.beforeFirst();

            while (rs.next()) {
                test = new Test();
                test.setName(rs.getString("NAME"));
                test.setSubject(rs.getString("SUBJECT"));
                test.setGenerated(rs.getTimestamp("GENERATED"));
                test.setTestID(rs.getInt("TESTID"));
                test.setUserID(rs.getInt("USERID"));
                test.setCategoryID(rs.getInt("CATEGORYID"));

                test.setQuestionPoints(qpDao.getQuestionPointsByTestID(test
                        .getTestID()));

                testList.add(test);
            }

            rs.close();

            return testList;

        } catch (SQLException sqle) {
            rs = null;
            pst = null;
            test = null;
            testList = null;
            throw sqle;
        }
    }

    @Override
    public void updateTest(Test toUpdate) throws SQLException {
        try {

            if (toUpdate.getCategoryID() == null)
                pst = con.prepareStatement(sqlUpdateRoot);
            else
                pst = con.prepareStatement(sqlUpdate);

            pst.setString(1, toUpdate.getName());
            pst.setString(2, toUpdate.getSubject());
            pst.setTimestamp(3, toUpdate.getGenerated());
            pst.setInt(4, toUpdate.getUserID());
            if (toUpdate.getCategoryID() == null)
                pst.setInt(5, toUpdate.getTestID());
            else {
                pst.setInt(5, toUpdate.getCategoryID());
                pst.setInt(6, toUpdate.getTestID());
            }
            pst.execute();

            qpDao.updateQuestionPoints(toUpdate.getTestID(), toUpdate
                    .getQuestionPoints());

        } catch (SQLException sqle) {
            pst = null;
            throw sqle;
        }
    }
}
